 --select * from FN_GET_LOT_INFO ('-1', 'Assembly', 'Assembly' ) 
 
 ALTER  FUNCTION FN_GET_LOT_INFO( @LOT_ID VARCHAR(50), @PROCESS_TYPE VARCHAR(50), @PS_TYPE VARCHAR(50) )
 
 RETURNS @FN_RESULT TABLE( LOT_ID INT, PROCESSSTEP_ID INT, LOCATION_ID INT , PART_NUMBER VARCHAR(50), LOT_NUMBER VARCHAR(50), LOT_CURRENT_STATUS VARCHAR(50), WAFER_QTY_IN INT, 
 WAFER_QTY_OUT INT, DIE_QTY_IN INT, DIE_QTY_OUT INT,  DATE_IN VARCHAR(50), DATE_OUT VARCHAR(50), PO_NO VARCHAR(50), Shop_Order_No int,
 FAB_LOT_NUMBER VARCHAR(50), VENDOR_LOT_NUMBER VARCHAR(50), WAFER_ID VARCHAR(100), 
 LOT_CATEGORY VARCHAR(50), LOT_DATE_CODE VARCHAR(50), LOT_COMMENTS VARCHAR(3000), ASSEMBLY_VENDOR VARCHAR(50) )
 
 AS
 
 BEGIN
 	
 	DECLARE @LOT_NUMBER		VARCHAR(50)
 	DECLARE @LOT_CURRENT_STATUS	VARCHAR(50)
 	DECLARE @WAFER_QTY_IN		INT
 	DECLARE @WAFER_QTY_OUT		INT
 	DECLARE @DIE_QTY_IN		INT
 	DECLARE @DIE_QTY_OUT		INT
 	DECLARE @DATE_IN		VARCHAR(50)
 	DECLARE @DATE_OUT		VARCHAR(50)
 	DECLARE @PO_NUMBER		VARCHAR(50)
 	DECLARE @FAB_LOT_NUMBER		VARCHAR(50)
 	DECLARE @VENDOR_LOT_NUMBER	VARCHAR(50)
 	DECLARE @WAFER_ID		VARCHAR(100)
 	DECLARE @LOT_CATEGORY		VARCHAR(50)
 	DECLARE @LOT_DATE_CODE		VARCHAR(50)
 	DECLARE @LOT_COMMENTS		VARCHAR(3000)
 	DECLARE @SHOP_ORDER_NO		INT	
 	DECLARE @ASSEMBLY_VENDOR	VARCHAR(50)
	DECLARE @PART_NUMBER		VARCHAR(50)
	DECLARE @NEW_LOT_ID 		INT
	DECLARE @PROCESSSTEP_ID	    INT
	DECLARE @LOCATION_ID		INT 

	DECLARE @LOTCURSOR CURSOR
	
 	IF @PROCESS_TYPE = 'FAB'
 	BEGIN
 				
		IF @LOT_ID <> 'ALL'
		BEGIN
 			SELECT @LOT_NUMBER = LOT_NUMBER, @LOT_CURRENT_STATUS = LOT_CURRENT_STATUS, @WAFER_QTY_IN = WAFER_QTY_IN,
 				   @WAFER_QTY_OUT = WAFER_QTY_OUT, @DATE_IN = CONVERT(VARCHAR,DATE_IN,101),
 				   @DATE_OUT = CONVERT(VARCHAR,DATE_OUT,101), @FAB_LOT_NUMBER = FAB_LOT_NUMBER, 
 				   @VENDOR_LOT_NUMBER = VENDOR_LOT_NUMBER, @WAFER_ID = WAFER_ID, @LOT_CATEGORY = LOT_CATEGORY, 
 				   @LOT_DATE_CODE = LOT_DATE_CODE, @LOT_COMMENTS = LOT_COMMENTS, @PO_NUMBER = PO_NUMBER,  
 				   @Shop_Order_No = Shop_Order_NUMBER FROM LOT_TABLE LT, FAB_LOT FL, PROCESS_STEP PS 
 				   WHERE LT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND LT.LOT_ID = FL.LOT_ID AND LT.LOT_ID = @LOT_ID
				
			INSERT INTO @FN_RESULT VALUES( @LOT_ID, NULL, NULL, NULL, @LOT_NUMBER, @LOT_CURRENT_STATUS, @WAFER_QTY_IN, @WAFER_QTY_OUT, null, null,
 			    @DATE_IN, @DATE_OUT, @PO_NUMBER, @Shop_Order_No, @FAB_LOT_NUMBER, @VENDOR_LOT_NUMBER, @WAFER_ID, @LOT_CATEGORY,
 		            @LOT_DATE_CODE, @LOT_COMMENTS, @ASSEMBLY_VENDOR )
 		END
		ELSE
		BEGIN				
				SET @LOTCURSOR = CURSOR FOR select LT.LOT_ID, LT.PROCESSSTEP_ID, LOCATION_ID,PART_NUMBER, LOT_NUMBER, LOT_CURRENT_STATUS, WAFER_QTY_IN,WAFER_QTY_OUT, CONVERT(VARCHAR,DATE_IN,101),CONVERT(VARCHAR,DATE_OUT,101), FAB_LOT_NUMBER, 
 				   VENDOR_LOT_NUMBER, WAFER_ID, LOT_CATEGORY, LOT_DATE_CODE, LOT_COMMENTS, PO_NUMBER, Shop_Order_NUMBER FROM LOT_TABLE LT, FAB_LOT FL, PROCESS_STEP PS, PRODUCT P
 				   WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND LT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND LT.LOT_ID = FL.LOT_ID AND PS.PS_TYPE = @PS_TYPE AND LOT_CURRENT_STATUS <> 'Close'
				
				OPEN @LOTCURSOR
				FETCH NEXT FROM @LOTCURSOR INTO @NEW_LOT_ID, @PROCESSSTEP_ID, @LOCATION_ID, @PART_NUMBER, @LOT_NUMBER, @LOT_CURRENT_STATUS, @WAFER_QTY_IN, @WAFER_QTY_OUT, @DATE_IN, @DATE_OUT,@FAB_LOT_NUMBER,@VENDOR_LOT_NUMBER,@WAFER_ID,@LOT_CATEGORY,@LOT_DATE_CODE,@LOT_COMMENTS, @PO_NUMBER, @Shop_Order_No

				WHILE @@FETCH_STATUS = 0
				BEGIN
						INSERT INTO @FN_RESULT VALUES( @NEW_LOT_ID, @PROCESSSTEP_ID, @LOCATION_ID, @PART_NUMBER, @LOT_NUMBER, @LOT_CURRENT_STATUS, @WAFER_QTY_IN, @WAFER_QTY_OUT, null, null,
 									@DATE_IN, @DATE_OUT, @PO_NUMBER, @Shop_Order_No, @FAB_LOT_NUMBER, @VENDOR_LOT_NUMBER, @WAFER_ID, @LOT_CATEGORY,
 									@LOT_DATE_CODE, @LOT_COMMENTS,@ASSEMBLY_VENDOR )
				
					SET @LOT_NUMBER     = NULL
					SET @LOT_CURRENT_STATUS = NULL
					SET @WAFER_QTY_IN   = NULL
					SET @WAFER_QTY_OUT  = NULL
					SET @DATE_IN        = NULL
					SET @DATE_OUT	    = NULL
					SET @FAB_LOT_NUMBER = NULL
					SET @VENDOR_LOT_NUMBER = NULL
					SET @WAFER_ID       = NULL
					SET @LOT_CATEGORY   = NULL
					SET @LOT_DATE_CODE  = NULL
					SET @LOT_COMMENTS	= NULL
					SET @PO_NUMBER  = NULL
					SET @Shop_Order_No  = NULL
					SET @NEW_LOT_ID		= NULL 
					SET @PART_NUMBER	= NULL
					SET @PROCESSSTEP_ID	= NULL
					SET @LOCATION_ID	= NULL
					FETCH NEXT FROM @LOTCURSOR INTO @NEW_LOT_ID, @PROCESSSTEP_ID, @LOCATION_ID, @PART_NUMBER, @LOT_NUMBER, @LOT_CURRENT_STATUS, @WAFER_QTY_IN, @WAFER_QTY_OUT, @DATE_IN, @DATE_OUT,@FAB_LOT_NUMBER,@VENDOR_LOT_NUMBER,@WAFER_ID,@LOT_CATEGORY,@LOT_DATE_CODE,@LOT_COMMENTS, @PO_NUMBER, @Shop_Order_No
					
				END
				CLOSE @LOTCURSOR
				DEALLOCATE @LOTCURSOR
				
		END
	 		
 	END
 	ELSE IF @PROCESS_TYPE = 'BUMPING'
 	BEGIN
 		IF @LOT_ID <> 'ALL'
		BEGIN
 			SELECT @LOT_NUMBER = LOT_NUMBER, @LOT_CURRENT_STATUS = LOT_CURRENT_STATUS, @WAFER_QTY_IN = LOT_TABLE.Wafer_Qty_In,
 				   @WAFER_QTY_OUT = WAFER_QTY_OUT, @DATE_IN = CONVERT(VARCHAR,DATE_IN,101),
 				   @DATE_OUT = CONVERT(VARCHAR,DATE_OUT,101), @FAB_LOT_NUMBER = FAB_LOT_NUMBER, 
 				   @VENDOR_LOT_NUMBER = VENDOR_LOT_NUMBER, @WAFER_ID = WAFER_ID, @LOT_CATEGORY = LOT_CATEGORY, 
 				   @LOT_DATE_CODE = LOT_DATE_CODE, @LOT_COMMENTS = LOT_COMMENTS, @PO_NUMBER = PO_NUMBER,  
 				   @Shop_Order_No = SHOP_ORDER_NUMBER FROM LOT_TABLE, BUMP_LOT WHERE LOT_TABLE.LOT_ID = BUMP_LOT.LOT_ID
 				   AND LOT_TABLE.LOT_ID = @LOT_ID
	 
 			INSERT INTO @FN_RESULT VALUES( @LOT_ID, NULL, NULL, NULL, @LOT_NUMBER, @LOT_CURRENT_STATUS, @WAFER_QTY_IN, @WAFER_QTY_OUT, null, null,
 					@DATE_IN, @DATE_OUT, @PO_NUMBER, @Shop_Order_No, @FAB_LOT_NUMBER, @VENDOR_LOT_NUMBER, @WAFER_ID, @LOT_CATEGORY,
 						@LOT_DATE_CODE, @LOT_COMMENTS, @ASSEMBLY_VENDOR )
		END
		ELSE
		BEGIN				
				SET @LOTCURSOR = CURSOR FOR select LT.LOT_ID, LT.PROCESSSTEP_ID, LOCATION_ID, PART_NUMBER, LOT_NUMBER, LOT_CURRENT_STATUS, LT.WAFER_QTY_IN,WAFER_QTY_OUT, CONVERT(VARCHAR,DATE_IN,101),CONVERT(VARCHAR,DATE_OUT,101), FAB_LOT_NUMBER, 
 				   VENDOR_LOT_NUMBER, WAFER_ID, LOT_CATEGORY, LOT_DATE_CODE, LOT_COMMENTS, PO_NUMBER, Shop_Order_NUMBER FROM LOT_TABLE LT, BUMP_LOT BL, PROCESS_STEP PS, PRODUCT P
 				   WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID  AND LT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND LT.LOT_ID = BL.LOT_ID AND PS.PS_TYPE = @PS_TYPE AND LOT_CURRENT_STATUS <> 'Close'
		
				OPEN @LOTCURSOR
				FETCH NEXT FROM @LOTCURSOR INTO @NEW_LOT_ID,@PROCESSSTEP_ID, @LOCATION_ID, @PART_NUMBER, @LOT_NUMBER, @LOT_CURRENT_STATUS, @WAFER_QTY_IN, @WAFER_QTY_OUT, @DATE_IN, @DATE_OUT,@FAB_LOT_NUMBER,@VENDOR_LOT_NUMBER,@WAFER_ID,@LOT_CATEGORY,@LOT_DATE_CODE,@LOT_COMMENTS, @PO_NUMBER, @Shop_Order_No

				WHILE @@FETCH_STATUS = 0
				BEGIN
						INSERT INTO @FN_RESULT VALUES( @NEW_LOT_ID, @PROCESSSTEP_ID, @LOCATION_ID, @PART_NUMBER, @LOT_NUMBER, @LOT_CURRENT_STATUS, @WAFER_QTY_IN, @WAFER_QTY_OUT, null, null,
 									@DATE_IN, @DATE_OUT, @PO_NUMBER, @Shop_Order_No, @FAB_LOT_NUMBER, @VENDOR_LOT_NUMBER, @WAFER_ID, @LOT_CATEGORY,
 									@LOT_DATE_CODE, @LOT_COMMENTS,@ASSEMBLY_VENDOR )
				
					SET @LOT_NUMBER = NULL
					SET @LOT_CURRENT_STATUS = NULL
					SET @WAFER_QTY_IN  = NULL
					SET @WAFER_QTY_OUT = NULL
					SET @DATE_IN = NULL
					SET @DATE_OUT	  = NULL
					SET @FAB_LOT_NUMBER = NULL
					SET @VENDOR_LOT_NUMBER = NULL
					SET @WAFER_ID  = NULL
					SET @LOT_CATEGORY = NULL
					SET @LOT_DATE_CODE = NULL
					SET @LOT_COMMENTS	  = NULL
					SET @PO_NUMBER  = NULL
					SET @Shop_Order_No = NULL
					SET @NEW_LOT_ID		= NULL 
					SET @PART_NUMBER	= NULL
					SET @PROCESSSTEP_ID	= NULL
					SET @LOCATION_ID	= NULL
					FETCH NEXT FROM @LOTCURSOR INTO @NEW_LOT_ID, @PROCESSSTEP_ID, @LOCATION_ID, @PART_NUMBER, @LOT_NUMBER, @LOT_CURRENT_STATUS, @WAFER_QTY_IN, @WAFER_QTY_OUT, @DATE_IN, @DATE_OUT,@FAB_LOT_NUMBER,@VENDOR_LOT_NUMBER,@WAFER_ID,@LOT_CATEGORY,@LOT_DATE_CODE,@LOT_COMMENTS, @PO_NUMBER, @Shop_Order_No
					
				END
				CLOSE @LOTCURSOR
				DEALLOCATE @LOTCURSOR
				
		END		
 	END
 	ELSE IF @PROCESS_TYPE = 'CP' OR @PROCESS_TYPE = 'SORT'
 	BEGIN
 		IF @LOT_ID <> 'ALL'
		BEGIN
 			SELECT @LOT_NUMBER = LOT_NUMBER, @LOT_CURRENT_STATUS = LOT_CURRENT_STATUS, @WAFER_QTY_IN = WAFER_QTY_IN,
 				   @WAFER_QTY_OUT = WAFER_QTY_OUT, @DATE_IN = CONVERT(VARCHAR,DATE_IN,101),
 				   @DATE_OUT = CONVERT(VARCHAR,DATE_OUT,101), @FAB_LOT_NUMBER = FAB_LOT_NUMBER, 
 				   @VENDOR_LOT_NUMBER = VENDOR_LOT_NUMBER, @WAFER_ID = SL_WAFER_ID, @LOT_CATEGORY = LOT_CATEGORY, 
 				   @LOT_DATE_CODE = LOT_DATE_CODE, @LOT_COMMENTS = LOT_COMMENTS, @PO_NUMBER = PO_NUMBER,  
 				   @Shop_Order_No = SHOP_ORDER_NUMBER FROM LOT_TABLE, SORT_LOT WHERE LOT_TABLE.LOT_ID = SORT_LOT.LOT_ID 
 				   AND LOT_TABLE.LOT_ID = @LOT_ID		
	 
 			INSERT INTO @FN_RESULT VALUES( @LOT_ID, NULL, NULL, NULL, @LOT_NUMBER, @LOT_CURRENT_STATUS, @WAFER_QTY_IN, @WAFER_QTY_OUT, null, null,
 					@DATE_IN, @DATE_OUT, @PO_NUMBER, @Shop_Order_No, @FAB_LOT_NUMBER, @VENDOR_LOT_NUMBER, @WAFER_ID, @LOT_CATEGORY,
 						@LOT_DATE_CODE, @LOT_COMMENTS, @ASSEMBLY_VENDOR )
		END
		ELSE
		BEGIN				
				SET @LOTCURSOR = CURSOR FOR select LT.LOT_ID, LT.PROCESSSTEP_ID, LOCATION_ID, PART_NUMBER,LOT_NUMBER, LOT_CURRENT_STATUS, WAFER_QTY_IN,WAFER_QTY_OUT, CONVERT(VARCHAR,DATE_IN,101),CONVERT(VARCHAR,DATE_OUT,101), FAB_LOT_NUMBER, 
 				   VENDOR_LOT_NUMBER, SL_WAFER_ID, LOT_CATEGORY, LOT_DATE_CODE, LOT_COMMENTS, PO_NUMBER, Shop_Order_NUMBER FROM LOT_TABLE LT, SORT_LOT SL, PROCESS_STEP PS, PRODUCT P
 				   WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID  AND LT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND LT.LOT_ID = SL.LOT_ID AND PS.PS_TYPE = @PS_TYPE AND LOT_CURRENT_STATUS <> 'Close'
		
				OPEN @LOTCURSOR
				FETCH NEXT FROM @LOTCURSOR INTO @NEW_LOT_ID, @PROCESSSTEP_ID, @LOCATION_ID, @PART_NUMBER, @LOT_NUMBER, @LOT_CURRENT_STATUS, @WAFER_QTY_IN, @WAFER_QTY_OUT, @DATE_IN, @DATE_OUT,@FAB_LOT_NUMBER,@VENDOR_LOT_NUMBER,@WAFER_ID,@LOT_CATEGORY,@LOT_DATE_CODE,@LOT_COMMENTS, @PO_NUMBER, @Shop_Order_No

				WHILE @@FETCH_STATUS = 0
				BEGIN
						INSERT INTO @FN_RESULT VALUES( @NEW_LOT_ID, @PROCESSSTEP_ID, @LOCATION_ID, @PART_NUMBER, @LOT_NUMBER, @LOT_CURRENT_STATUS, @WAFER_QTY_IN, @WAFER_QTY_OUT, null, null,
 									@DATE_IN, @DATE_OUT, @PO_NUMBER, @Shop_Order_No, @FAB_LOT_NUMBER, @VENDOR_LOT_NUMBER, @WAFER_ID, @LOT_CATEGORY,
 									@LOT_DATE_CODE, @LOT_COMMENTS,@ASSEMBLY_VENDOR )
				
					SET @LOT_NUMBER = NULL
					SET @LOT_CURRENT_STATUS = NULL
					SET @WAFER_QTY_IN  = NULL
					SET @WAFER_QTY_OUT = NULL
					SET @DATE_IN = NULL
					SET @DATE_OUT	  = NULL
					SET @FAB_LOT_NUMBER = NULL
					SET @VENDOR_LOT_NUMBER = NULL
					SET @WAFER_ID  = NULL
					SET @LOT_CATEGORY = NULL
					SET @LOT_DATE_CODE = NULL
					SET @LOT_COMMENTS	  = NULL
					SET @PO_NUMBER  = NULL
					SET @Shop_Order_No = NULL
					SET @NEW_LOT_ID		= NULL 
					SET @PART_NUMBER	= NULL
					SET @PROCESSSTEP_ID	= NULL
					SET @LOCATION_ID	= NULL
					FETCH NEXT FROM @LOTCURSOR INTO @NEW_LOT_ID, @PROCESSSTEP_ID, @LOCATION_ID, @PART_NUMBER, @LOT_NUMBER, @LOT_CURRENT_STATUS, @WAFER_QTY_IN, @WAFER_QTY_OUT, @DATE_IN, @DATE_OUT,@FAB_LOT_NUMBER,@VENDOR_LOT_NUMBER,@WAFER_ID,@LOT_CATEGORY,@LOT_DATE_CODE,@LOT_COMMENTS, @PO_NUMBER, @Shop_Order_No
					
				END
				CLOSE @LOTCURSOR
				DEALLOCATE @LOTCURSOR
				
		END				
 	END
 	ELSE IF @PROCESS_TYPE = 'Assembly' OR @PROCESS_TYPE = 'TEST'
 	BEGIN
 		IF @LOT_ID <> 'ALL'
		BEGIN
 			SELECT @LOT_NUMBER = LOT_NUMBER, @LOT_CURRENT_STATUS = LOT_CURRENT_STATUS, @Die_QTY_IN = QTY_IN,
 				   @Die_QTY_OUT = QTY_OUT, @DATE_IN = CONVERT(VARCHAR,DATE_IN,101),
 				   @DATE_OUT = CONVERT(VARCHAR,DATE_OUT,101), @FAB_LOT_NUMBER = FAB_LOT_NUMBER, 
 				   @VENDOR_LOT_NUMBER = VENDOR_LOT_NUMBER, @LOT_CATEGORY = LOT_CATEGORY, 
 				   @LOT_DATE_CODE = LOT_DATE_CODE, @LOT_COMMENTS = LOT_COMMENTS, @PO_NUMBER = PO_Number,  
 				   @Shop_Order_No = Shop_Order_Number FROM LOT_TABLE Where LOT_TABLE.LOT_ID = @LOT_ID		
	 
 			INSERT INTO @FN_RESULT VALUES( @LOT_ID, NULL, NULL, NULL, @LOT_NUMBER, @LOT_CURRENT_STATUS, null, null, @Die_QTY_IN, @Die_QTY_OUT,
 					@DATE_IN, @DATE_OUT, @PO_NUMBER, @Shop_Order_No, @FAB_LOT_NUMBER, @VENDOR_LOT_NUMBER, null, @LOT_CATEGORY,
 						@LOT_DATE_CODE, @LOT_COMMENTS, @ASSEMBLY_VENDOR )
		END
		ELSE
		BEGIN				
				SET @LOTCURSOR = CURSOR FOR SELECT LT.LOT_ID, LT.PROCESSSTEP_ID, LOCATION_ID, PART_NUMBER, LOT_NUMBER, LOT_CURRENT_STATUS, QTY_IN, QTY_OUT, CONVERT(VARCHAR,DATE_IN,101),CONVERT(VARCHAR,DATE_OUT,101), FAB_LOT_NUMBER, 
 				   VENDOR_LOT_NUMBER, LOT_CATEGORY, LOT_DATE_CODE, LOT_COMMENTS, PO_NUMBER, Shop_Order_NUMBER FROM LOT_TABLE LT,PROCESS_STEP PS , PRODUCT P
 				   WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID  AND LT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PS.PS_TYPE = @PS_TYPE AND LOT_CURRENT_STATUS <> 'Close'
		
				OPEN @LOTCURSOR
				FETCH NEXT FROM @LOTCURSOR INTO @NEW_LOT_ID, @PROCESSSTEP_ID, @LOCATION_ID, @PART_NUMBER, @LOT_NUMBER, @LOT_CURRENT_STATUS, @Die_QTY_IN, @Die_QTY_OUT, @DATE_IN, @DATE_OUT,@FAB_LOT_NUMBER,@VENDOR_LOT_NUMBER,@LOT_CATEGORY,@LOT_DATE_CODE,@LOT_COMMENTS, @PO_NUMBER, @Shop_Order_No

				WHILE @@FETCH_STATUS = 0
				BEGIN
						INSERT INTO @FN_RESULT VALUES( @NEW_LOT_ID, @PROCESSSTEP_ID, @LOCATION_ID, @PART_NUMBER, @LOT_NUMBER, @LOT_CURRENT_STATUS, null, null, @Die_QTY_IN, @Die_QTY_OUT,
 								@DATE_IN, @DATE_OUT, @PO_NUMBER, @Shop_Order_No, @FAB_LOT_NUMBER, @VENDOR_LOT_NUMBER, null, @LOT_CATEGORY,
 						@LOT_DATE_CODE, @LOT_COMMENTS, @ASSEMBLY_VENDOR )
				
					SET @LOT_NUMBER = NULL
					SET @LOT_CURRENT_STATUS = NULL
					SET @Die_QTY_IN  = NULL
					SET @Die_QTY_OUT = NULL
					SET @DATE_IN = NULL
					SET @DATE_OUT	  = NULL
					SET @FAB_LOT_NUMBER = NULL
					SET @VENDOR_LOT_NUMBER = NULL
					SET @LOT_CATEGORY = NULL
					SET @LOT_DATE_CODE = NULL
					SET @LOT_COMMENTS	  = NULL
					SET @PO_NUMBER  = NULL
					SET @Shop_Order_No = NULL
					SET @NEW_LOT_ID		= NULL 
					SET @PART_NUMBER	= NULL
					SET @PROCESSSTEP_ID	= NULL
					SET @LOCATION_ID	= NULL
					FETCH NEXT FROM @LOTCURSOR INTO @NEW_LOT_ID, @PROCESSSTEP_ID, @LOCATION_ID, @PART_NUMBER, @LOT_NUMBER, @LOT_CURRENT_STATUS, @Die_QTY_IN, @Die_QTY_OUT, @DATE_IN, @DATE_OUT,@FAB_LOT_NUMBER,@VENDOR_LOT_NUMBER,@LOT_CATEGORY,@LOT_DATE_CODE,@LOT_COMMENTS, @PO_NUMBER, @Shop_Order_No
					
				END
				CLOSE @LOTCURSOR
				DEALLOCATE @LOTCURSOR
				
		END						
 	END
 	ELSE
 	BEGIN
 		IF @LOT_ID <> 'ALL'
		BEGIN
			SELECT @LOT_NUMBER = LOT_NUMBER, @LOT_CURRENT_STATUS = LOT_CURRENT_STATUS, @DIE_QTY_IN = QTY_IN,
 				   @DIE_QTY_OUT = QTY_OUT, @WAFER_QTY_IN = WAFER_QTY_IN, @WAFER_QTY_OUT = WAFER_QTY_OUT, 
 				   @DATE_IN = CONVERT(VARCHAR,DATE_IN,101), @DATE_OUT = CONVERT(VARCHAR,DATE_OUT,101), 
 				   @FAB_LOT_NUMBER = FAB_LOT_NUMBER, @VENDOR_LOT_NUMBER = VENDOR_LOT_NUMBER, @WAFER_ID = WAFER_ID, 
 				   @LOT_CATEGORY = LOT_CATEGORY, @LOT_DATE_CODE = LOT_DATE_CODE, @LOT_COMMENTS = LOT_COMMENTS, @ASSEMBLY_VENDOR = ASSEMBLY_VENDOR 
 				   FROM LOT_TABLE, INVENTORY_LOT WHERE LOT_TABLE.LOT_ID = INVENTORY_LOT.LOT_ID AND LOT_TABLE.LOT_ID = @LOT_ID		
	 
 			INSERT INTO @FN_RESULT VALUES( @LOT_ID, NULL, NULL, NULL, @LOT_NUMBER, @LOT_CURRENT_STATUS, @WAFER_QTY_IN, @WAFER_QTY_OUT, @DIE_QTY_IN, @DIE_QTY_OUT,
 					@DATE_IN, @DATE_OUT, NULL, NULL, @FAB_LOT_NUMBER, @VENDOR_LOT_NUMBER, @WAFER_ID, @LOT_CATEGORY,
 						@LOT_DATE_CODE, @LOT_COMMENTS, @ASSEMBLY_VENDOR )
		END
		ELSE
		BEGIN	
				IF @PROCESS_TYPE = 'FG' OR @PROCESS_TYPE = 'Finished Goods'
				BEGIN					
					SET @LOTCURSOR = CURSOR FOR select LT.LOT_ID, LT.PROCESSSTEP_ID, LOCATION_ID, PART_NUMBER, LOT_NUMBER, LOT_CURRENT_STATUS, ISNULL(IL_QTY_BALANCE,QTY_IN), QTY_OUT, WAFER_QTY_IN,WAFER_QTY_OUT, CONVERT(VARCHAR,DATE_IN,101),CONVERT(VARCHAR,DATE_OUT,101), FAB_LOT_NUMBER, 
 					VENDOR_LOT_NUMBER, WAFER_ID, LOT_CATEGORY, LOT_DATE_CODE, LOT_COMMENTS, ASSEMBLY_VENDOR FROM LOT_TABLE LT, INVENTORY_LOT IL, PROCESS_STEP PS , PRODUCT P
 					WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID  AND LT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND LT.LOT_ID = IL.LOT_ID AND PS.PS_TYPE = @PS_TYPE AND LOT_CURRENT_STATUS <> 'Close' AND ISNULL(IL_QTY_BALANCE,QTY_IN) > 0
				END
				ELSE
				BEGIN
						SET @LOTCURSOR = CURSOR FOR select LT.LOT_ID, LT.PROCESSSTEP_ID, LOCATION_ID, PART_NUMBER, LOT_NUMBER, LOT_CURRENT_STATUS, QTY_IN, QTY_OUT, WAFER_QTY_IN,WAFER_QTY_OUT, CONVERT(VARCHAR,DATE_IN,101),CONVERT(VARCHAR,DATE_OUT,101), FAB_LOT_NUMBER, 
 					VENDOR_LOT_NUMBER, WAFER_ID, LOT_CATEGORY, LOT_DATE_CODE, LOT_COMMENTS, ASSEMBLY_VENDOR FROM LOT_TABLE LT, INVENTORY_LOT IL, PROCESS_STEP PS , PRODUCT P
 					WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID  AND LT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND LT.LOT_ID = IL.LOT_ID AND PS.PS_TYPE = @PS_TYPE AND LOT_CURRENT_STATUS <> 'Close' AND (QTY_IN > 0 OR WAFER_QTY_IN > 0)
				END
				OPEN @LOTCURSOR
				FETCH NEXT FROM @LOTCURSOR INTO @NEW_LOT_ID, @PROCESSSTEP_ID, @LOCATION_ID, @PART_NUMBER, @LOT_NUMBER, @LOT_CURRENT_STATUS, @DIE_QTY_IN, @DIE_QTY_OUT, @WAFER_QTY_IN, @WAFER_QTY_OUT, @DATE_IN, @DATE_OUT,@FAB_LOT_NUMBER,@VENDOR_LOT_NUMBER,@WAFER_ID,@LOT_CATEGORY,@LOT_DATE_CODE,@LOT_COMMENTS,@ASSEMBLY_VENDOR 

				WHILE @@FETCH_STATUS = 0
				BEGIN
						INSERT INTO @FN_RESULT VALUES( @NEW_LOT_ID, @PROCESSSTEP_ID, @LOCATION_ID, @PART_NUMBER, @LOT_NUMBER, @LOT_CURRENT_STATUS, @WAFER_QTY_IN, @WAFER_QTY_OUT, @DIE_QTY_IN, @DIE_QTY_OUT,
 									@DATE_IN, @DATE_OUT, NULL, NULL, @FAB_LOT_NUMBER, @VENDOR_LOT_NUMBER, @WAFER_ID, @LOT_CATEGORY,
 									@LOT_DATE_CODE, @LOT_COMMENTS, @ASSEMBLY_VENDOR )
				
					SET @LOT_NUMBER = NULL
					SET @LOT_CURRENT_STATUS = NULL
					SET @DIE_QTY_IN  = NULL
					SET @DIE_QTY_OUT = NULL
					SET @WAFER_QTY_IN  = NULL
					SET @WAFER_QTY_OUT = NULL
					SET @DATE_IN = NULL
					SET @DATE_OUT	  = NULL
					SET @FAB_LOT_NUMBER = NULL
					SET @VENDOR_LOT_NUMBER = NULL
					SET @WAFER_ID  = NULL
					SET @LOT_CATEGORY = NULL
					SET @LOT_DATE_CODE = NULL
					SET @LOT_COMMENTS	  = NULL
					SET @ASSEMBLY_VENDOR  = NULL
					SET @NEW_LOT_ID		= NULL 
					SET @PART_NUMBER	= NULL
					SET @PROCESSSTEP_ID	= NULL
					SET @LOCATION_ID	= NULL
					FETCH NEXT FROM @LOTCURSOR INTO @NEW_LOT_ID, @PROCESSSTEP_ID, @LOCATION_ID, @PART_NUMBER, @LOT_NUMBER, @LOT_CURRENT_STATUS, @DIE_QTY_IN, @DIE_QTY_OUT, @WAFER_QTY_IN, @WAFER_QTY_OUT, @DATE_IN, @DATE_OUT,@FAB_LOT_NUMBER,@VENDOR_LOT_NUMBER,@WAFER_ID,@LOT_CATEGORY,@LOT_DATE_CODE,@LOT_COMMENTS,@ASSEMBLY_VENDOR 
					
				END
				CLOSE @LOTCURSOR
				DEALLOCATE @LOTCURSOR
				
		END				
 	END
 
 RETURN
 
 END
